const express = require("express");
const pool = require("../pool.js");
//创建路由器
const router = express.Router();
//暴露出去
// 不限制监听数量
// process.setMaxListeners(0);

module.exports = router;

//1.地图/省份查询
//地址:http://127.0.0.1:3000/data/index/swiper
router.get("/swiper", (req, res, next) => {
  let sql = "select * from f_nav";
  pool.query(sql, (err, lbt) => {
    if (lbt[0]) {
      res.send({ code: 201, msg: "查询成功", data: lbt });
    } else {
      res.send({ code: 200, msg: "查询失败" });
    }
  });
});
//2.八大菜系(mac系统不支持group by 分组查询)
//地址:http://127.0.0.1:3000/data/index/style
// router.get("/style", (req, res, next) => {
//   let p = req.query.style;
//   let sql =
//     "select a.area,a.style,a.name,b.image from f_style a  left join f_image b on a.name=b.image_name where a.style=? group by a.name";
//   pool.query(sql, p, (err, result) => {
//     if (result[0]) {
//       res.send({ code: 201, msg: "查询成功", data: result });
//     } else {
//       res.send({ code: 200, msg: "查找失败" });
//     }
//   });
// });
//2.八大菜系
//地址:http://127.0.0.1:3000/data/index/style
router.get("/style", (req, res, next) => {
  let p = req.query.style;
  let sql =
    "select a.area,a.style,a.name,b.image from f_style a  left join f_image b on a.name=b.image_name where a.style=? ";
  pool.query(sql, p, (err, result) => {
    if (result[0]) {
      let a = [result[result.length - 1]];
      for (let i = result.length - 1; i > 0; i--) {
        if (result[i].name !== result[i - 1].name) {
          a.push(result[i - 1]);
        }
        if (i == 1) {
          res.send({ code: 201, msg: "查询成功", data: a });
        }
      }
    } else {
      res.send({ code: 200, msg: "查找失败" });
    }
  });
});

//3.地球自加载
// 地址:http://127.0.0.1:3000/data/index/global
router.get("/global", (req, res, next) => {
  let sql = "select B_kind from f_ball";
  pool.query(sql, (err, result) => {
    if (result[0]) {
      res.send({ code: 201, msg: "查询成功", data: result });
    } else {
      res.send({ code: 200, msg: "查找失败" });
    }
  });
});

//4.省份及省内城市包含食物详情
//地址:http://127.0.0.1:3000/data/index/prov
router.get("/prov", (req, res, next) => {
  let p = req.query.prov;
  let sql = "select id,prov,lovend,detail from f_nav where prov=?";
  pool.query(sql, p, (err, prov_detail) => {
    let sql =
      "select a.city,a.lovend,b.name,b.detail from f_city a join f_style b on a.city=b.area where a.prov=? order by a.city ";
    pool.query(sql, p, (err, foods) => {
      res.send({ code: 201, msg: "查询成功", data: { prov_detail, foods } });
    });
  });
});

//5.城市美食美称和图片查询
//地址:http://127.0.0.1:3000/data/index/city_food
router.get("/city_food", (req, res, next) => {
  let food = req.query.food;
  let sql =
    "select a.D_name,a.D_lovend,b.image from f_detail a join f_image b on a.D_name=b.image_name where a.D_name=? ";
  pool.query(sql, food, (err, result) => {
    if (result[0]) {
      let a = [result[result.length - 1]];
      for (let i = result.length - 1; i > 0; i--) {
        if (result[i].name !== result[i - 1].name) {
          a.push(result[i - 1]);
        }
        if (i == 1) {
          res.send({ code: 201, msg: "查询成功", data: a });
        }
      }
    } else {
      res.send({ code: 200, msg: "查找失败" });
    }
  });
});

//6.美食详情查询
//地址:http://127.0.0.1:3000/data/index/food_detail
router.get("/food_detail/:name", (req, res, next) => {
  let name = req.params.name;
  let sql = `select a.name,a.detail,a.area,b.prov from f_style a join f_city b on a.area=b.city where a.name like '%${name}%'`;
  pool.query(sql, (err, detail) => {
    let sql = `select * from f_detail where D_name like '%${name}%' `;
    pool.query(sql, (err, history) => {
      // res.send({ code: 200, msg: "查询成功", data: result });
      let sql = `select * from f_process where T_name like '%${name}%'`;
      pool.query(sql, (err, process) => {
        let sql = `select * from f_steps where S_name like '%${name}%'`;
        pool.query(sql, (err, step) => {
          let sql = `select * from f_information where I_name like '%${name}%'`;
          pool.query(sql, (err, info) => {
            let sql = `select * from f_image where image_name  like '%${name}%'`;
            pool.query(sql, (err, img) => {
              if (!history && !process && !step && !info && !img) {
                res.send({ code: 200, msg: "查询失败" });
              } else {
                res.send({
                  code: 201,
                  msg: "查询成功",
                  data: { detail, history, process, step, info, img },
                });
              }
            });
          });
        });
      });
    });
  });
});

//7.美食搜索加载数据
//地址: http://127.0.0.1:3000/data/index/search
router.get("/search", (req, res, next) => {
  let prov = req.query.prov;
  let sql = "select distinct kind from f_style ";
  pool.query(sql, (err, kinds) => {
    let sql = "select distinct taste from f_style ";
    pool.query(sql, (err, t) => {
      let sql = "select city from f_city where prov=?";
      pool.query(sql, prov, (err, c) => {
        res.send({
          code: 201,
          msg: "查询成功",
          data: { kinds, taste: t, cities: c },
        });
      });
    });
  });
});

// 8.美食分类和口味查询
// 地址:http://127.0.0.1:3000/data/index/search_taste
router.get("/search_taste", (req, res, next) => {
  let kind = req.query.kind;
  let taste = req.query.taste;
  let count = req.query.count;
  if (kind && taste) {
    let sql =
      "select a.name,a.area,a.detail,b.image,a.kind from f_style a join f_image b on a.name=b.image_name where a.kind=?  and a.taste=?";
    pool.query(sql, [kind, taste], (err, result) => {
      if (result[0]) {
        let a = [result[result.length - 1]];
        for (let i = result.length - 1; i > 0; i--) {
          if (result[i].name !== result[i - 1].name) {
            a.push(result[i - 1]);
          }
          if (i == 1) {
            res.send({ code: 201, msg: "查询成功", data: a });
          }
        }
      } else {
        res.send({ code: 200, msg: "查找失败" });
      }
    });
  } else if (!kind && taste) {
    let sql =
      "select a.name,a.area,a.detail,b.image,a.kind from f_style a join f_image b on a.name=b.image_name where a.taste=? ";
    pool.query(sql, [taste], (err, result) => {
      if (result[0]) {
        let a = [result[result.length - 1]];
        for (let i = result.length - 1; i > 0; i--) {
          if (result[i].name !== result[i - 1].name) {
            a.push(result[i - 1]);
          }
          if (i == 1) {
            res.send({ code: 201, msg: "查询成功", data: a });
          }
        }
      } else {
        res.send({ code: 200, msg: "查找失败" });
      }
    });
  } else if (!taste && kind) {
    let sql =
      "select a.name,a.area,a.detail,b.image,a.kind from f_style a join f_image b on a.name=b.image_name where a.kind=?";
    pool.query(sql, [kind], (err, result) => {
      if (result[0]) {
        let a = [result[result.length - 1]];
        for (let i = result.length - 1; i > 0; i--) {
          if (result[i].name !== result[i - 1].name) {
            a.push(result[i - 1]);
          }
          if (i == 1) {
            res.send({ code: 201, msg: "查询成功", data: a });
          }
        }
      } else {
        res.send({ code: 200, msg: "查找失败" });
      }
    });
  } else if (!taste && !kind) {
    let sql = `select a.name,a.area,a.detail,b.image,a.kind from f_style a join f_image b on a.name=b.image_name `;
    pool.query(sql, (err, result) => {
      if (result[0]) {
        let a = [result[result.length - 1]];
        for (let i = result.length - 1; i > 0; i--) {
          if (result[i].name !== result[i - 1].name) {
            a.push(result[i - 1]);
          }
          if (i == 1) {
            res.send({ code: 201, msg: "查询成功", data: a });
          }
        }
      } else {
        res.send({ code: 200, msg: "查找失败" });
      }
    });
  }
});

// 9.城市详情查询
// 地址:http://127.0.0.1:3000/data/index/city_detail
router.get("/city_detail", (req, res, next) => {
  let city = req.query.city;
  let sql = "select * from f_city where city=?";
  pool.query(sql, city, (err, result) => {
    if (result[0]) {
      res.send({ code: 201, msg: "查询成功", data: result });
    } else {
      res.send({ code: 200, msg: "查询失败" });
    }
  });
});

//10.搜索框的名字模糊搜索
// 地址:http://127.0.0.1:3000/data/index/searchFood
router.get("/searchFood", (req, res, next) => {
  let food = req.query.food;
  let sql = `select a.name,a.area,a.detail,a.kind,b.image from f_style a join f_image b on a.name=b.image_name where a.name like "%${food}%"`;
  pool.query(sql, (err, result) => {
    if (result[0]) {
      let a = [result[result.length - 1]];
      for (let i = result.length - 1; i > 0; i--) {
        if (result[i].name !== result[i - 1].name) {
          a.push(result[i - 1]);
        }
        if (i == 1) {
          res.send({ code: 201, msg: "查询成功", data: a });
        }
      }
    } else {
      res.send({ code: 200, msg: "查找失败" });
    }
  });
});

// 11.地球查询
// 地址:http://127.0.0.1:3000/data/index/global_search
router.get("/global_search", (req, res, next) => {
  let k = req.query.kind;
  let sql =
    "select a.B_kind,a.B_detail,a.B_score,b.name from f_ball a join f_style b on a.B_kind=b.kind where a.B_kind=?";
  pool.query(sql, k, (err, data) => {
    let sql =
      "select a.name,b.image from f_style a   join f_image  b on a.name=b.image_name where a.kind=?";
    pool.query(sql, k, (err, result) => {
      if (result[0]) {
        let a = [result[result.length - 1]];
        for (let i = result.length - 1; i > 0; i--) {
          if (result[i].name !== result[i - 1].name) {
            a.push(result[i - 1]);
          }
          if (i == 1) {
            [{ B_kind: kind, B_detail: detail, B_score: score }] = data;
            if (data[0]) {
              res.send({
                code: 201,
                msg: "查询成功",
                data: { kind, detail, score, images: a },
              });
            } else {
              res.send({ code: 200, msg: "查询失败" });
            }
          }
        }
      } else {
        res.send({ code: 200, msg: "查找失败" });
      }
    });
  });
});

//12.分页查询
// http://127.0.0.1:3000/data/index/page?page=1&size=5
router.get("/page", (req, res, next) => {
  let page = req.query.page;
  let size = req.query.size;
  let sql = `select a.name,a.area,a.detail,b.image,a.kind from f_style a join f_image b on a.name=b.image_name `;
  pool.query(sql, (err, result) => {
    if (result[0]) {
      let a = [result[result.length - 1]];
      for (let i = result.length - 1; i > 0; i--) {
        if (result[i].name !== result[i - 1].name) {
          a.push(result[i - 1]);
        }
        if (i == 1) {
          if (!page) page = 1;
          if (!size) size = 6;
          let b = a.slice((page - 1) * size, page * size);
          res.send({ code: 201, msg: "查询成功", data: b });
        }
      }
    } else {
      res.send({ code: 200, msg: "查找失败" });
    }
  });
});

// 13.删除菜品
// http://127.0.0.1:3000/data/index/food_delete
router.get('/food_delete',(req,res,next)=>{
	let name=req.query.name
	let sql='delete from f_style where name=?'
	pool.query(sql,name,(err,r)=>{
		if(r.affectedRows==0){
			res.send({code:200,msg:'删除失败'})
		}else{
			let sql='delete from f_image where image_name=?'
			pool.query(sql,name,(err,re)=>{
				if(re.affectedRows==0){
					res.send({code:200,msg:'删除失败'})		
				}else{
					res.send({code:201,msg:'删除成功'})
				}
			})	
		}
		
	})
})

//14.修改菜品信息
//http://127.0.0.1:3000/data/index/edit_food
router.post('/edit_food',(req,res,next)=>{
	let area=req.body.area
	let detail=req.body.detail
	let kind=req.body.kind
	let name=req.body.name
	let sql='update area=?,detail=?,kind=? from f_style where name=?'
	pool.query(sql,[area,detail,kind,name],(err,r)=>{
		if(err) next(err)
		if(r.changedRows==1){
			res.send({code:201,mag:"修改成功"})
		}else{
			res.send({code:200,mag:"修改失败"})
		}
	})
})
//15.新增菜品
//http://127.0.0.1:3000/data/index/insert_food
router.post('/insert_food',(req,res,next)=>{
	let name=req.body.name
	let kind=req.body.kind
	let area=req.body.area
	let detail=req.body.detail
	let sql='insert into f_style(id,name,kind,area,detail) values(null,?,?,?,?)'
	pool.query(sql,[name,kind,area,detail],(err,r)=>{
		if(r.affectedRows==1){
			res.send({code:201,msg:'修改成功'})
		}else{
			res.send({code:200,msg:'修改失败'})
		}
	})
})
//15.新增菜品
//http://127.0.0.1:3000/data/index/insertImg_food
router.post('/insertImg_food',(req,res,next)=>{
	let name=req.body.name
	let img=req.body.image
	let sql='insert into f_image(id,image_name,image) values(null,?,?)'
	pool.query(sql,[name,img],(err,r)=>{
		if(r.affectedRows==1){
			res.send({code:201,msg:'添加成功'})
		}else{
			res.send({code:200,msg:'添加失败'})
		}
	})
})